package cn.edu.qlnu.xky.test.dao;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import cn.edu.qlnu.xky.test.common.Page;
/**
 * 不能出现具体的涉及到某个表或某个模块的内容
 * jdbc对象必须在本类中使用，不能出现在其他类中
 */
public class BaseDao {

	private Connection conn = null;
	private Statement stmt = null;
	private ResultSet rs = null;

	private static String driver;
	private static String url;
	private static String user;
	private static String psw;

	static {
		//这是静态代码段，会在第一次访问本类的时候执行，后边会一直驻留内存
		System.out.println("读取数据库连接信息……");
		Properties pro = new Properties();
		try {
			InputStream in = BaseDao.class.getClassLoader().getResourceAsStream("db.properties");//new FileInputStream("db.properties");
			pro.load(in);
			driver = pro.getProperty("driver");
			url=pro.getProperty("url");
			user=pro.getProperty("user");
			psw=pro.getProperty("psw");
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public BaseDao() {
		// 完成数据库的公共操作
		try {
			//实现驱动的注册
			Class.forName(driver);
			//创建连接对象，提示：连接字符串和用户名、密码放在java代码中，如果数据库服务器发生变化，密码重新设置；
			//要修改代码，重新编译部署？如何解决？
			conn=DriverManager.getConnection(url,user,psw);
			//创建statement对象
			stmt=conn.createStatement();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	/**
	 * 根据传递的参数sql，可以是insert update delete
	 * 来执行，返回执行结果 int类型
	 * @param sql
	 * @return
	 */
	public int doAddEditDelete(String sql) {
		System.out.println("BaseDao daoAddEditDelete's sql="+sql);
		if(stmt == null)
			return -1;
		try {
			return stmt.executeUpdate(sql);
		} catch (SQLException e) {
			e.printStackTrace();
			return -1;
		}finally {
			close();
		}
	}
	
	
	public List<Map<String,Object>> searchByPage(String sql,int pageNo){
		Page p = new Page();
		sql +=" limit "+(pageNo-1)*p.getPageSize()+","+p.getPageSize();
		return searchBySql(sql);
	}
	
	public int searchCount(String sql) {
		System.out.println("BaseDao searchCount sql="+sql);
		int ret = 0;
		if(stmt != null) {
			try {
				rs = stmt.executeQuery(sql);
				if(rs.next()) {
					ret = rs.getInt(1);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				close();
			}
		}			
		return ret;
	}
	
	
	public List<Map<String,Object>> searchBySql(String sql){
		List<Map<String,Object>> objs = new ArrayList<Map<String,Object>>();
		System.out.println("BaseDao searchBySql's sql="+sql);
		if(stmt != null) {
			try {
				rs = stmt.executeQuery(sql);
				ResultSetMetaData rsmd = rs.getMetaData();
				Map<String,Object> obj = null;
				while(rs.next()) {
					obj = new HashMap<String,Object>();
					for(int i=1;i<=rsmd.getColumnCount();i++) {
						obj.put(rsmd.getColumnName(i), rs.getObject(i));
					}
					objs.add(obj);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}finally {
				close();
			}
		}			
		return objs;
	}

	private void close() {
		try {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public boolean isClosed() {
		try {
			return (rs == null || rs.isClosed())
					|| (stmt == null || stmt.isClosed())
					|| (conn == null || conn.isClosed())
					;
		} catch (SQLException e) {
			e.printStackTrace();
			return true;
		}
	}
}
